package zy.dao.buy.fee.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.buy.fee.BuyFeeDAO;
import zy.dto.buy.fee.BuyFeeReportDto;
import zy.entity.buy.fee.T_Buy_Fee;
import zy.entity.buy.fee.T_Buy_FeeList;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class BuyFeeDAOImpl extends BaseDaoImpl implements BuyFeeDAO{
	@Override
	public Integer count(Map<String, Object> params) {
		Object fe_ar_state = params.get("fe_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object fe_supply_code = params.get("fe_supply_code");
		Object fe_manager = params.get("fe_manager");
		Object fe_number = params.get("fe_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_buy_fee t");
		sql.append(" JOIN t_buy_supply sp ON sp_code = fe_supply_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(fe_ar_state)) {
			sql.append(" AND fe_ar_state = :fe_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND fe_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND fe_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(fe_supply_code)) {
			sql.append(" AND fe_supply_code = :fe_supply_code ");
		}
		if (StringUtil.isNotEmpty(fe_manager)) {
			sql.append(" AND fe_manager = :fe_manager ");
		}
		if (StringUtil.isNotEmpty(fe_number)) {
			sql.append(" AND INSTR(fe_number,:fe_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Buy_Fee> list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object fe_ar_state = params.get("fe_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object fe_supply_code = params.get("fe_supply_code");
		Object fe_manager = params.get("fe_manager");
		Object fe_number = params.get("fe_number");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT fe_id,fe_number,fe_supply_code,fe_maker,fe_manager,fe_date,fe_ar_state,fe_ar_date,");
		sql.append(" fe_money,fe_discount_money,fe_pay_state,fe_payable,fe_payabled,fe_prepay,fe_remark,fe_sysdate,fe_us_id,t.companyid,");
		sql.append(" sp_name AS supply_name");
		sql.append(" FROM t_buy_fee t");
		sql.append(" JOIN t_buy_supply sp ON sp_code = fe_supply_code AND sp.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(fe_ar_state)) {
			sql.append(" AND fe_ar_state = :fe_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND fe_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND fe_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(fe_supply_code)) {
			sql.append(" AND fe_supply_code = :fe_supply_code ");
		}
		if (StringUtil.isNotEmpty(fe_manager)) {
			sql.append(" AND fe_manager = :fe_manager ");
		}
		if (StringUtil.isNotEmpty(fe_number)) {
			sql.append(" AND INSTR(fe_number,:fe_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY fe_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Buy_Fee.class));
	}

	@Override
	public T_Buy_Fee load(Integer fe_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT fe_id,fe_number,fe_supply_code,fe_maker,fe_manager,fe_date,fe_ar_state,fe_ar_date,");
		sql.append(" fe_money,fe_discount_money,fe_pay_state,fe_payable,fe_payabled,fe_prepay,fe_remark,fe_sysdate,fe_us_id,t.companyid,");
		sql.append(" sp_name AS supply_name");
		sql.append(" FROM t_buy_fee t");
		sql.append(" JOIN t_buy_supply sp ON sp_code = fe_supply_code AND sp.companyid = t.companyid");
		sql.append(" WHERE fe_id = :fe_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("fe_id", fe_id),
					new BeanPropertyRowMapper<>(T_Buy_Fee.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Buy_Fee load(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT fe_id,fe_number,fe_supply_code,fe_maker,fe_manager,fe_date,fe_ar_state,fe_ar_date,");
		sql.append(" fe_money,fe_discount_money,fe_pay_state,fe_payable,fe_payabled,fe_prepay,fe_remark,fe_sysdate,fe_us_id,t.companyid,");
		sql.append(" sp_name AS supply_name");
		sql.append(" FROM t_buy_fee t");
		sql.append(" JOIN t_buy_supply sp ON sp_code = fe_supply_code AND sp.companyid = t.companyid");
		sql.append(" WHERE fe_number = :fe_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("fe_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Buy_Fee.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public T_Buy_Fee check(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT fe_id,fe_number,fe_supply_code,fe_maker,fe_manager,fe_date,fe_ar_state,fe_ar_date,");
		sql.append(" fe_money,fe_discount_money,fe_pay_state,fe_payable,fe_payabled,fe_prepay,fe_remark,fe_us_id,t.companyid");
		sql.append(" FROM t_buy_fee t");
		sql.append(" WHERE fe_number = :fe_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("fe_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Buy_Fee.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public List<T_Buy_FeeList> temp_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT fel_id,fel_mp_code,fel_money,fel_remark,fel_us_id,companyid,");
		sql.append(" (SELECT pp_name FROM t_money_property pp WHERE pp_type = 0 AND pp_code = fel_mp_code AND pp.companyid = t.companyid LIMIT 1) AS mp_name");
		sql.append(" FROM t_buy_feelist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND fel_us_id = :fel_us_id");
		sql.append(" AND companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY fel_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Buy_FeeList.class));
	}
	
	@Override
	public List<String> temp_check(Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT fel_mp_code");
		sql.append(" FROM t_buy_feelist_temp t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND fel_us_id = :fel_us_id");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), new MapSqlParameterSource().addValue("fel_us_id", us_id).addValue("companyid", companyid), String.class);
	}

	@Override
	public void temp_save(List<T_Buy_FeeList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_buy_feelist_temp");
		sql.append(" (fel_mp_code,fel_money,fel_remark,fel_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:fel_mp_code,:fel_money,:fel_remark,:fel_us_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_updateMoney(T_Buy_FeeList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_feelist_temp");
		sql.append(" SET fel_money = :fel_money");
		sql.append(" WHERE 1=1");
		sql.append(" AND fel_id = :fel_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_updateRemark(T_Buy_FeeList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_feelist_temp");
		sql.append(" SET fel_remark = :fel_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND fel_id = :fel_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void temp_del(Integer fel_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_buy_feelist_temp");
		sql.append(" WHERE fel_id=:fel_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("fel_id", fel_id));
	}
	
	@Override
	public void temp_clear(Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_buy_feelist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND fel_us_id = :fel_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("fel_us_id", us_id).addValue("companyid", companyid));
	}
	
	@Override
	public List<T_Buy_FeeList> detail_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT fel_id,fel_number,fel_mp_code,fel_money,fel_remark,companyid,");
		sql.append(" (SELECT pp_name FROM t_money_property pp WHERE pp_type = 0 AND pp_code = fel_mp_code AND pp.companyid = t.companyid LIMIT 1) AS mp_name");
		sql.append(" FROM t_buy_feelist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND fel_number = :fel_number");
		sql.append(" AND companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY fel_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Buy_FeeList.class));
	}
	
	@Override
	public List<T_Buy_FeeList> detail_list_forsavetemp(String fe_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT fel_id,fel_number,fel_mp_code,fel_money,fel_remark,companyid");
		sql.append(" FROM t_buy_feelist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND fel_number = :fel_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" ORDER BY fel_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("fel_number", fe_number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Buy_FeeList.class));
	}
	
	@Override
	public void save(T_Buy_Fee fee, List<T_Buy_FeeList> details) {
		String prefix = CommonUtil.NUMBER_PREFIX_BUY_FEE + DateUtil.getYearMonthDateYYYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(fe_number))) AS new_number");
		sql.append(" FROM t_buy_fee");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(fe_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", fee.getCompanyid()), String.class);
		fee.setFe_number(new_number);
		sql.setLength(0);
		sql.append(" INSERT INTO t_buy_fee");
		sql.append(" (fe_number,fe_supply_code,fe_maker,fe_manager,fe_date,fe_ar_state,fe_ar_date,");
		sql.append(" fe_money,fe_discount_money,fe_pay_state,fe_payable,fe_payabled,fe_prepay,fe_remark,fe_sysdate,fe_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:fe_number,:fe_supply_code,:fe_maker,:fe_manager,:fe_date,:fe_ar_state,:fe_ar_date,");
		sql.append(" :fe_money,:fe_discount_money,:fe_pay_state,:fe_payable,:fe_payabled,:fe_prepay,:fe_remark,:fe_sysdate,:fe_us_id,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(fee),holder);
		fee.setFe_id(holder.getKey().intValue());
		for(T_Buy_FeeList item:details){
			item.setFel_number(fee.getFe_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_buy_feelist");
		sql.append(" (fel_number,fel_mp_code,fel_money,fel_remark,companyid)");
		sql.append(" VALUES");
		sql.append(" (:fel_number,:fel_mp_code,:fel_money,:fel_remark,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void update(T_Buy_Fee fee, List<T_Buy_FeeList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_buy_fee");
		sql.append(" SET fe_date=:fe_date");
		sql.append(" ,fe_supply_code=:fe_supply_code");
		sql.append(" ,fe_maker=:fe_maker");
		sql.append(" ,fe_manager=:fe_manager");
		sql.append(" ,fe_money=:fe_money");
		sql.append(" ,fe_discount_money=:fe_discount_money");
		sql.append(" ,fe_payable=:fe_payable");
		sql.append(" ,fe_remark=:fe_remark");
		sql.append(" ,fe_ar_state=:fe_ar_state");
		sql.append(" ,fe_ar_date=:fe_ar_date");
		sql.append(" ,fe_us_id=:fe_us_id");
		sql.append(" WHERE fe_id=:fe_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(fee));
		for(T_Buy_FeeList item:details){
			item.setFel_number(fee.getFe_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_buy_feelist");
		sql.append(" (fel_number,fel_mp_code,fel_money,fel_remark,companyid)");
		sql.append(" VALUES");
		sql.append(" (:fel_number,:fel_mp_code,:fel_money,:fel_remark,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void updateApprove(T_Buy_Fee fee) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_buy_fee");
		sql.append(" SET fe_ar_state=:fe_ar_state");
		sql.append(" ,fe_ar_date = :fe_ar_date");
		sql.append(" WHERE fe_id=:fe_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(fee));
	}
	
	@Override
	public void del(String fe_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_buy_fee");
		sql.append(" WHERE fe_number=:fe_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("fe_number", fe_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_buy_feelist");
		sql.append(" WHERE fel_number=:fel_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("fel_number", fe_number).addValue("companyid", companyid));
	}
	
	@Override
	public void deleteList(String fe_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_buy_feelist");
		sql.append(" WHERE fel_number=:fel_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("fel_number", fe_number).addValue("companyid", companyid));
	}
	
	private String getReportSQL(Map<String, Object> params){
		String timeType = StringUtil.trimString(params.get("timeType"));
		StringBuffer sql = new StringBuffer();
		if(StringUtil.isNotEmpty(params.get("fel_mp_code"))){
			sql.append(" AND fel_mp_code = :fel_mp_code ");
		}
		if(StringUtil.isNotEmpty(params.get("fe_supply_code"))){
			sql.append(" AND fe_supply_code = :fe_supply_code ");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			if("1".equals(timeType)){
				sql.append(" AND fe_ar_date >= :begindate ");
			}else {
				sql.append(" AND fe_date >= :begindate ");
			}
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			if("1".equals(timeType)){
				sql.append(" AND fe_ar_date <= :enddate ");
			}else {
				sql.append(" AND fe_date <= :enddate ");
			}
		}
		if(StringUtil.isNotEmpty(params.get("fe_ar_state"))){
			sql.append(" AND fe_ar_state = :fe_ar_state ");
		}
		if(StringUtil.isNotEmpty(params.get("fe_pay_state"))){
			sql.append(" AND fe_pay_state = :fe_pay_state ");
		}
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	@Override
	public List<BuyFeeReportDto> listReport(Map<String, Object> params) {
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		if("supply".equals(type)){
			sql.append(" fe_supply_code AS code,");
			sql.append(" (SELECT sp_name FROM t_buy_supply sp WHERE sp_code = fe_supply_code AND sp.companyid = t.companyid LIMIT 1) AS name,");
		}else if("property".equals(type)){
			sql.append(" fel_mp_code AS code,");
			sql.append(" (SELECT pp_name FROM t_money_property pp WHERE pp_type = 0 AND pp_code = fel_mp_code AND pp.companyid = t.companyid LIMIT 1) AS name,");
		}
		sql.append(" SUM(fel_money) AS money");
		sql.append(" FROM t_buy_fee t");
		sql.append(" JOIN t_buy_feelist fel ON fel_number = fe_number AND fel.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getReportSQL(params));
		if("supply".equals(type)){
			sql.append(" GROUP BY fe_supply_code");
		}else if("property".equals(type)){
			sql.append(" GROUP BY fel_mp_code");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(BuyFeeReportDto.class));
	}
	
	@Override
	public List<BuyFeeReportDto> listReportDetail(Map<String, Object> params) {
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		if("supply".equals(type)){
			sql.append(" fe_supply_code AS code,");
			sql.append(" fe_date AS date,");
			sql.append(" (SELECT sp_name FROM t_buy_supply sp WHERE sp_code = fe_supply_code AND sp.companyid = t.companyid LIMIT 1) AS name,");
			sql.append(" fel_money AS money");
		}else if("property".equals(type)){
			sql.append(" fel_mp_code AS code,");
			sql.append(" (SELECT pp_name FROM t_money_property pp WHERE pp_type = 0 AND pp_code = fel_mp_code AND pp.companyid = t.companyid LIMIT 1) AS name,");
			sql.append(" SUM(fel_money) AS money");
		}
		
		sql.append(" FROM t_buy_fee t");
		sql.append(" JOIN t_buy_feelist fel ON fel_number = fe_number AND fel.companyid = t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(getReportSQL(params));
		if("property".equals(type)){
			sql.append(" GROUP BY fel_mp_code");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(BuyFeeReportDto.class));
	}
	
}
